﻿using System;
using System.Linq;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Wordprocessing;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Data;
using System.Collections.Generic;

namespace OpenXmlSdkSample
{
    class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            // TODO: Implement Functionality Here
            //CreateSpreadsheetWorkbook("E:/123.xlsx");
            //ExcelHelper.CreateSpreadsheetWorkbook("E:/123.xlsx", "Sheet1");
            //ExcelHelper.WriteData("E:/123.xlsx", "Sheet1", ExcelHelper.GetScores(10));
            //ExcelHelper.MergeTwoCells("E:/123.xlsx", "Sheet1", "A1", "A2");
            //ExcelHelper.MergeTwoCells("E:/123.xlsx", "Sheet1", "B1", "C1");
            //ExcelHelper.MergeTwoCells("E:/123.xlsx", "Sheet1", "C1", "D1");

            //Export1();
            ExcelExport export = new ExcelExport();

            DataTable dt = DataCreator.GetDataTable(DataCreator.GetSingleHeaderMergeRow());
            export.ExportDataTable(dt, "D:/123.xlsx");


            //DataTable dt = DataCreator.GetDataTable(DataCreator.GetSingleHeaderMergeRow());
            //var tt = export.GetMergeRow(dt, 0, dt.Rows.Count - 1, 0, dt.Columns.Count - 1, 1);

            Console.Write("Press any key to continue . . . ");
            Console.ReadKey(true);
        }


        private static void Export1()
        {
            DataTable data = new DataTable();
            for (int i = 0; i < 14; i++)
            {
                DataColumn col = new DataColumn("col" + i, typeof(string));
                data.Columns.Add(col);
            }

            //表头
            DataRow row = data.NewRow();
            row[0] = "人员姓名";
            row[1] = "人员性质";
            row[2] = "服务时间";
            row[3] = "服务类别";
            row[4] = "服务项目";
            row[5] = "班次";
            row[6] = "小时数";
            row[7] = "客户姓名";
            row[8] = "区号";
            row[9] = "编号";
            row[10] = "所属公司";
            row[11] = "调整金额";
            row[12] = "结算金额";
            row[13] = "备注";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "王娜娜";
            row[1] = "加盟";
            row[2] = "42605";
            row[3] = "居家护理";
            row[4] = "母婴9500";
            row[5] = "全天";
            row[6] = "24";
            row[7] = "刘金凤";
            row[8] = "AB";
            row[9] = "AB";
            row[10] = "坤新";
            row[11] = "-91.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "";
            row[1] = "";
            row[2] = "42605";
            row[3] = "护工";
            row[4] = "母婴9500";
            row[5] = "全天";
            row[6] = "24";
            row[7] = "李四";
            row[8] = "A01";
            row[9] = "B01";
            row[10] = "坤新";
            row[11] = "-100.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "";
            row[1] = "";
            row[2] = "小计";
            row[3] = "";
            row[4] = "";
            row[5] = "";
            row[6] = "24";
            row[7] = "1人";
            row[8] = "";
            row[9] = "";
            row[10] = "";
            row[11] = "-91.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "张三三";
            row[1] = "加盟";
            row[2] = "42605";
            row[3] = "居家护理";
            row[4] = "母婴8500";
            row[5] = "全天";
            row[6] = "24";
            row[7] = "刘金凤";
            row[8] = "AB";
            row[9] = "AB";
            row[10] = "坤新";
            row[11] = "-81.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "";
            row[1] = "";
            row[2] = "小计";
            row[3] = "";
            row[4] = "";
            row[5] = "";
            row[6] = "24";
            row[7] = "1人";
            row[8] = "";
            row[9] = "";
            row[10] = "";
            row[11] = "-81.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);

            row = data.NewRow();
            row[0] = "合计";
            row[1] = "";
            row[2] = "";
            row[3] = "";
            row[4] = "";
            row[5] = "";
            row[6] = "48";
            row[7] = "2人";
            row[8] = "";
            row[9] = "";
            row[10] = "";
            row[11] = "-91.25";
            row[12] = "0";
            row[13] = "";
            data.Rows.Add(row);


            List<string> mergeCells = new List<string>();
            if (data.Rows.Count > 3)
            {
                //合计
                string item = string.Format("{0}:{1}", "A" + data.Rows.Count, "B" + data.Rows.Count);
                mergeCells.Add(item);
                //去掉首尾两行
                int mergeRowIndex = 0;
                int maxRowIndex = data.Rows.Count;
                for (int i = 1; i < maxRowIndex; i++)
                {
                    if (!String.IsNullOrEmpty(data.Rows[i][0].ToString()))
                    {
                        int beginIndex = mergeRowIndex + 1;
                        int endIndex = i;
                        if (beginIndex != endIndex)
                        {
                            //人员姓名
                            item = string.Format("{0}:{1}", "A" + beginIndex, "A" + endIndex);
                            mergeCells.Add(item);
                            //人员性质
                            item = string.Format("{0}:{1}", "B" + beginIndex, "B" + endIndex);
                            mergeCells.Add(item);
                        }

                        mergeRowIndex = i;
                    }


                }
            }

            ExcelExportModel model = new ExcelExportModel
            {
                Data = data,
                MergeCell = mergeCells
            };

            new GeneratedExport().CreatePackage("e:/test.xlsx", model);
        }

        public static void CreateWordDoc(string filepath, string msg)
        {
            using (WordprocessingDocument doc = WordprocessingDocument.Create(filepath, WordprocessingDocumentType.Document))
            {
                // Add a main document part. 
                MainDocumentPart mainPart = doc.AddMainDocumentPart();

                // Create the document structure and add some text.
                mainPart.Document = new Document();
                Body body = mainPart.Document.AppendChild(new Body());
                Paragraph para = body.AppendChild(new Paragraph());
                DocumentFormat.OpenXml.Wordprocessing.Run run = para.AppendChild(new DocumentFormat.OpenXml.Wordprocessing.Run());

                // String msg contains the text, "Hello, Word!"
                run.AppendChild(new DocumentFormat.OpenXml.Wordprocessing.Text(msg));
            }
        }

        public static void CreateSpreadsheetWorkbook(string filepath)
        {
            // Create a spreadsheet document by supplying the filepath.
            // By default, AutoSave = true, Editable = true, and Type = xlsx.
            SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

            // Add a WorkbookPart to the document.
            WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
            workbookpart.Workbook = new Workbook();

            // Add a WorksheetPart to the WorkbookPart.
            WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet(new SheetData());

            // Add Sheets to the Workbook.
            Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

            // Append a new worksheet and associate it with the workbook.
            Sheet sheet = new Sheet()
            {
                Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name = "Sheet1"
            };



            sheets.Append(sheet);


            workbookpart.Workbook.Save();

            // Close the document.
            spreadsheetDocument.Close();
        }

        private static void WriteExcel(SheetData sheetData, string header, UInt32 index, string text)
        {
            Row r1 = sheetData.Descendants<Row>().Where(a => a.RowIndex == index).FirstOrDefault();
            if (r1 == null)
            {
                r1 = new Row() { RowIndex = index };
                sheetData.Append(r1);
            }
            r1.Append(CreateTextCell(header, index, text));
        }
        private static Cell CreateTextCell(string header, UInt32 index, string text)
        {
            var cell = new Cell
            {
                DataType = CellValues.InlineString,
                CellReference = header + index
            };
            var istring = new InlineString();
            var t = new DocumentFormat.OpenXml.Spreadsheet.Text { Text = text };
            istring.AppendChild(t);
            cell.AppendChild(istring);
            return cell;
        }

    }
}
